create database ATMsystems01
go
use ATMsystems01
go
create table Card
(
	IDCard varchar(13) primary key,
	PIN varchar(6)
	
)
go
create table UserAcc
(
	IDAcc varchar(20) primary key,
	NameAcc nvarchar(40),
	IDCard varchar(13),
	IDTrans varchar(15),
	Balance int,
	Status bit,
    Constraint keyIDCard Foreign key (IDCard) References Card(IDCard)
)
go
create table Trans
(
	IDTrans varchar(15) primary key,
	IDAcc varchar(20),
	Loai nvarchar(20),
	Mount int,
	AccTrans varchar(20),
	Date datetime,
	Constraint keyIDAcc Foreign key (IDAcc) References UserAcc(IDAcc)
)
go
insert into Card values('1111111111111','1111')
go
insert into UserAcc values('0','hai','1111111111111','1',2000000,1)
go
insert into Trans values('1','0','chuyen khoan',100000,'122','2/4/2012')
go
select *from Card
select *from UserAcc
select *from Trans
go
--
create proc sp_Card
@IDCard int,
	@PIN int
as
select *from Card
go
create proc sp_get_Card
	@IDAcc varchar(20),
	@NameAcc nvarchar(40),
	@IDCard varchar(13),
	@IDTrans varchar(15),
	@Balance int,
	@Status bit
as
select *from UserAcc
go
create proc sp_get_UserAcc
as
select Balance from UserAcc
go
create proc doimaPIN
@IDCard int,
@PIN int
as
update Card set PIN=@PIN where IDCard=@IDCard
go
insert into Card values('1111111111112','1111')
go
insert into UserAcc values('1','huong','1111111111112','null',2000000,0)
go
insert into Card values('2222222222222','1111')
go
insert into UserAcc values('2','quynh','2222222222222','null',20000000,1)
go
insert into Trans values('3','0','rutien',100000,'122','2/4/2012')